CREATE DATABASE AptitudeTest
ALTER AUTHORIZATION ON DATABASE::AptitudeTest TO [sa];
GO
USE AptitudeTest
GO 
CREATE TABLE Manager
(
	[id]			INT IDENTITY,
	[user]			VARCHAR(50) PRIMARY KEY NOT NULL, 
	[pass]			VARCHAR(50) NOT NULL,
	[name]			NVARCHAR(100) NOT NULL,
	[email]			VARCHAR(100) NOT NULL,
	[phone]			varchar(50),
	[permission]	VARCHAR(50) NOT NULL,
	[avatar]		VARCHAR(50)
)
GO
INSERT INTO Manager VALUES('admin','lueSGJZetyySpUndWjMBEg==','Admin','admin@gmail.com',0,'Administrator','');
GO
CREATE TABLE Candidate
(
	[id]			INT IDENTITY,
	[email]			VARCHAR(100) PRIMARY KEY NOT NULL, 
	[pass]			VARCHAR(50) NOT NULL,
	[name]			NVARCHAR(100) NOT NULL,
	[birthday]		CHAR(10) NOT NULL,
	[address]		NVARCHAR(500) NOT NULL,
	[city]			NVARCHAR(100) NOT NULL,
	[country]		NVARCHAR(100) NOT NULL,
	[description]	NVARCHAR(MAX) NOT NULL,
	[phone]			VARCHAR(50),
	[avatar]		VARCHAR(50),
	[dateregister]	DATETIME
)
GO
CREATE TABLE Questions
(
	[id]			INT IDENTITY,
	[type]			NVARCHAR(100) NOT NULL,
	[quesCode]		VARCHAR(30) PRIMARY KEY NOT NULL,	
	[content]		NVARCHAR(MAX) NOT NULL,
	[answer]		char(1) NOT NULL
)
GO
CREATE TABLE ExamCode
(
	[id]			INT IDENTITY,
	[examCode]		VARCHAR(30) PRIMARY KEY NOT NULL,
	[type]			NVARCHAR(100) NOT NULL,
)
GO
CREATE TABLE Exam
(
	[id]			INT IDENTITY,
	[examCode]		VARCHAR(30) NOT NULL,
	[quesCode]		VARCHAR(30) NOT NULL,
)
GO
CREATE TABLE Tests
(
	[id]			INT IDENTITY PRIMARY KEY,
	[email]			VARCHAR(100) NOT NULL,
	[codelogin]		VARCHAR(30) NOT NULL,
	[pass]			VARCHAR(30) NOT NULL,
	[round]			CHAR(1) NOT NULL,
	[datetest]		CHAR(10) NOT NULL,
	[timestart]		char(5) NOT NULL,
	[examtest]		VARCHAR(30)	NOT NULL,
	[answertest]	VARCHAR(200),
	[results]		int,
	[timecompleted] VARCHAR(20),
	[status]		VARCHAR(20)
)